/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package request.report;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import system.general.Exc;
import system.general.Logged;
import system.general.Message;
import system.general.Persistencia;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "request_generalReport")
@RequestScoped
public class General {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Message msg = new Message();
    private ResultSet rsts;
    private String sql;
    public String detailTable;
    public String headerTable;
    public String reportName;
    private request.detail.Find request_Find = new request.detail.Find();

    public General() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
        detailTable = "reportes.solicitud_detalle_" + String.valueOf(logged.getLoggedId());
        headerTable = "reportes.solicitud_general_header_" + String.valueOf(logged.getLoggedId());
        reportName = "solicitud_general.jasper";
    }

    public void curriculum() throws Exc, SQLException, NamingException, Exception {
        try {
            Map params = new HashMap();
            system.general.Path path = new system.general.Path();
            //Elimina la tabla
            dropTable();
            //Crea la tabla 
            createTable();
            //inserta los datos
            loadData();
            //lee las propiedades y carga el mapa
            params.put("detailTable", detailTable);
            params.put("headerTable", headerTable);
            params.put("SUBREPORT_DIR", path.getRequestReportPath());
            params.put("logoPath", path.getLogoPathView());
            params.put("reportPath", path.getReportPathView());
            String jasperReport = path.getRequestReportPath() + reportName;
            this.persistencia.dbConnect();
            createReport(jasperReport, params, persistencia.getConn());
        } catch (JRException ex) {
        } catch (Exception ex) {
        } finally {
            this.persistencia.dbDisconnect();
        }
    }

    public Properties leerReportProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("label_" + logged.getLoggedIdiom() + ".properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    public Properties leerProperties() {
        Properties propiedades = new Properties();
        try {
            propiedades.load(getClass().getResourceAsStream("/request/detail/label_" + logged.getLoggedIdiom() + ".properties"));
            if (propiedades.isEmpty()) {
                return null;
            }
        } catch (Exception ex) {
            msg.showErr(namePackage(), "java", ex);
        } finally {
            return propiedades;
        }
    }

    private void loadData() throws Exc, SQLException, NamingException, Exception {
        try {
            int respuesta;
            int pregunta;
            String descripcion_pregunta;
            String descripcion_respuesta;
            boolean checked;
            Map map = new LinkedHashMap();


            String universidad_nombre, carrera, recibido;
            String universidad_sede;
            Date fecha_supervision = new Date();


            sql = "SELECT  pg.nombre || ' ' || pg.apellido AS recepcionista, ug.nombre AS universidad_nombre, "
                    + "us.nombre AS sede, sg.carrera, sg.recibido, sg.registrado AS fecha FROM solicitud_general sg "
                    + "INNER JOIN solicitud_sede ss ON (sg.id = ss.id_solicitud) "
                    + "INNER JOIN universidad_sede us ON (ss.id_sede = us.id) "
                    + "INNER JOIN persona_general pg ON (pg.id = sg.id_recepcionista) "
                    + "INNER JOIN universidad_facultad uf ON (sg.id_facultad = uf.id) "
                    + "INNER JOIN universidad_general ug ON (ug.id = uf.id_universidad) "
                    + "WHERE sg.id =" + logged.getMasterId();


            rsts = persistencia.executeRsts(sql);
            rsts.next();
            universidad_nombre = rsts.getString("universidad_nombre");
            universidad_sede = rsts.getString("sede");
            fecha_supervision = rsts.getDate("fecha");
            carrera = rsts.getString("carrera");
            recibido = rsts.getString("recepcionista");



            Properties mp = leerReportProperties();
            sql = "INSERT INTO " + headerTable + " "
                    + "(etiqueta1, detalle1,"
                    + " etiqueta2, detalle2,"
                    + " etiqueta3, detalle3,"
                    + " etiqueta4, detalle4,"
                    + " etiqueta5, detalle5,"
                    + " etiqueta6, detalle6,"
                    + " etiqueta7, detalle7,"
                    + " etiqueta8, detalle8,"
                    + " etiqueta9, detalle9,"
                    + " etiqueta10, detalle10,"
                    + " etiqueta11, detalle11,"
                    + " etiqueta12, detalle12,"
                    + " etiqueta13, detalle13,"
                    + " etiqueta14, detalle14,"
                    + " etiqueta15, detalle15,"
                    + " etiqueta16, detalle16,"
                    + " etiqueta17, detalle17,"
                    + " etiqueta18, detalle18,"
                    + " etiqueta19, detalle19,"
                    + " etiqueta20, detalle20,"
                    + " etiqueta21, detalle21)"
                    + " VALUES ("
                    + "'" + mp.getProperty("title0") + "','',"
                    + "'" + mp.getProperty("field1") + "','',"
                    + "'" + mp.getProperty("field2") + "','" + fecha_supervision + "',"
                    + "'" + mp.getProperty("field3") + "','" + recibido + "',"
                    + "'" + mp.getProperty("field14") + "','',"
                    + "'" + mp.getProperty("field5") + "','',"
                    + "'" + mp.getProperty("field6") + "','" + universidad_sede + "',"
                    + "'" + mp.getProperty("field7") + "','" + carrera + "',"
                    + "'" + mp.getProperty("field8") + "','',"
                    + "'" + mp.getProperty("field9") + "','',"
                    + "'" + mp.getProperty("field10") + "','',"
                    + "'" + mp.getProperty("field11") + "','',"
                    + "'" + mp.getProperty("field12") + "','',"
                    + "'" + mp.getProperty("field13") + "','',"
                    + "'" + mp.getProperty("field14") + "','',"
                    + "'" + mp.getProperty("field15") + "','',"
                    + "'" + mp.getProperty("field16") + "','',"
                    + "'" + mp.getProperty("field17") + "','',"
                    + "'" + mp.getProperty("field18") + "','',"
                    + "'" + mp.getProperty("field19") + "','',"
                    + "'" + mp.getProperty("field20") + "','')";

            persistencia.executeUpdate(sql);
            mp.clear();
            mp = leerProperties();
            for (Enumeration idl_pregunta = mp.keys(); idl_pregunta.hasMoreElements();) {
                Object obj = idl_pregunta.nextElement();
                String questionKey = obj.toString();
                if (questionKey.startsWith("pregunta")) {
                    String codigo = questionKey.substring(8);
                    descripcion_pregunta = mp.getProperty(questionKey);
                    pregunta = Integer.parseInt(codigo);

                    for (Enumeration idl_respuesta = mp.keys(); idl_respuesta.hasMoreElements();) {
                        Object objr = idl_respuesta.nextElement();
                        String answersKey = objr.toString();
                        if (answersKey.startsWith("respuesta" + codigo)) {
                            respuesta = Integer.parseInt(answersKey.substring(11));
                            descripcion_respuesta = mp.getProperty(answersKey);

                            map.clear();
                            map.put("id_solicitud = " + logged.getMasterId(), " AND ");
                            map.put("idl_pregunta = " + pregunta, " AND ");
                            map.put("idl_respuesta = " + respuesta, "");
                            checked = request_Find.byExist(map);

                            sql = "INSERT INTO " + detailTable + " "
                                    + "(pregunta, respuesta, descripcion_pregunta, descripcion_respuesta, checked) "
                                    + "VALUES (" + pregunta + ", "
                                    + respuesta + ","
                                    + "'" + descripcion_pregunta + "',"
                                    + "'" + descripcion_respuesta + "',"
                                    + checked + ")";
                            persistencia.executeUpdate(sql);
                        }
                    }
                }
            }
        } finally {
        }
    }

    private void dropTable() throws Exc, SQLException, NamingException, Exception {
        sql = "DROP TABLE IF EXISTS " + headerTable + "," + detailTable;
        persistencia.executeUpdate(sql);
    }

    private void createTable() throws Exc, SQLException, NamingException, Exception {
        sql = "CREATE TABLE " + detailTable
                + " (pregunta integer, "
                + "respuesta integer,"
                + "descripcion_pregunta text,"
                + "descripcion_respuesta text,"
                + "checked boolean) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + detailTable
                + " OWNER TO core;";

        sql += " CREATE TABLE " + headerTable
                + " (etiqueta1 text,"
                + "detalle1 text, "
                + "etiqueta2 text, "
                + "detalle2 text, "
                + "etiqueta3 text, "
                + "detalle3 text, "
                + "etiqueta4 text, "
                + "detalle4 text, "
                + "etiqueta5 text, "
                + "detalle5 text, "
                + "etiqueta6 text, "
                + "detalle6 text, "
                + "etiqueta7 text, "
                + "detalle7 text, "
                + "etiqueta8 text, "
                + "detalle8 text, "
                + "etiqueta9 text, "
                + "detalle9 text, "
                + "etiqueta10 text, "
                + "detalle10 text, "
                + "etiqueta11 text, "
                + "detalle11 text, "
                + "etiqueta12 text, "
                + "detalle12 text, "
                + "etiqueta13 text, "
                + "detalle13 text, "
                + "etiqueta14 text, "
                + "detalle14 text, "
                + "etiqueta15 text, "
                + "detalle15 text, "
                + "etiqueta16  text, "
                + "detalle16 text, "
                + "etiqueta17 text, "
                + "detalle17 text, "
                + "etiqueta18 text, "
                + "detalle18 text, "
                + "etiqueta19 text, "
                + "detalle19 text, "
                + "etiqueta20 text, "
                + "detalle20 text, "
                + "etiqueta21 text, "
                + "detalle21 text) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + headerTable
                + " OWNER TO core;";


        persistencia.executeUpdate(sql);
    }

    public void createReport(String archivoJasper, Map params, Connection ds) throws JRException, IOException {
        JasperPrint jasperPrint = JasperFillManager.fillReport(archivoJasper, params, ds);
        HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, servletOutputStream);
    }

    private String namePackage() {
        Package pack = this.getClass().getPackage();
        return pack.getName();
    }
}
